from flask import Flask, render_template, request, flash, redirect, url_for
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.secret_key = "test"

# 设置数据库连接地址
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://root:mysql@127.0.0.1:3306/test19"
# 设置是否追踪数据库变化（一旦开启，非常消耗性能）
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False

db = SQLAlchemy(app)


# 作者表
class Author(db.Model):
    __tablename__ = "authors"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    books = db.relationship("Book", backref="author")


# 书表
class Book(db.Model):
    __tablename__ = "books"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), unique=True)
    # 设置外键
    author_id = db.Column(db.Integer, db.ForeignKey("authors.id"))


@app.route('/', methods=["GET", "POST"])
def index():
    if request.method == "GET":
        try:
            authors = Author.query.all()
        except BaseException as e:
            print(e)
            flash("数据库查询错误")
            return redirect(url_for("index"))

        # 没错就执行
        return render_template("book_test-1.html", authors=authors)

    # post 处理，取值
    author_name = request.form.get("author_name")
    book_name = request.form.get("book_name")

    # 校验
    if not all([author_name, book_name]):
        flash("参数不足")
        return redirect(url_for("index"))

    # 判断是否有该作者
    author = Author.query.filter_by(name=author_name).first()

    # 判断是否有该书
    book = Book.query.filter_by(name=book_name).first()
    # 如果存在
    if book:
        flash("该书已存在")

        return redirect(url_for("index"))

    # 异常处理
    try:

        if author:
            book = Book(name=book_name)
            author.books.append(book)
            db.session.add(book)
            db.session.commit()
        else:
            author = Author(name=author_name)
            book = Book(name=book_name)
            author.books.append(book)
            db.session.add_all([author, book])
            db.session.commit()

    except BaseException as e:
        print(e)
        flash("数据库错误")
        db.session.rollback()
        # 重定向
        return redirect(url_for("index"))

    return redirect(url_for("index"))


# 删除书籍
@app.route('/delete_book/<int:book_id>')
def delete_book(book_id):
    book = Book.query.get(book_id)
    db.session.delete(book)
    db.session.commit()
    return redirect(url_for("index"))


# 删除作者
@app.route('/delete_author/<int:author_id>')
def delete_author(author_id):
    try:
        author = Author.query.get(author_id)
        # 先删除该作者的所有书
        for book in author.books:
            db.session.delete(book)
        # 再删作者
        db.session.delete(author)

        db.session.commit()

    except BaseException as e:
        print(e)
        flash("数据库错误")
        db.session.rollback()
        return redirect(url_for("index"))

    return redirect(url_for("index"))


if __name__ == '__main__':
    db.drop_all()
    db.create_all()
    # 生成数据
    au1 = Author(name='老王')
    au2 = Author(name='老尹')
    au3 = Author(name='老刘')
    # 把数据提交给用户会话
    db.session.add_all([au1, au2, au3])
    # 提交会话
    db.session.commit()
    bk1 = Book(name='老王回忆录', author_id=au1.id)
    bk2 = Book(name='我读书少，你别骗我', author_id=au1.id)
    bk3 = Book(name='如何才能让自己更骚', author_id=au2.id)
    bk4 = Book(name='怎样征服美丽少女', author_id=au3.id)
    bk5 = Book(name='如何征服英俊少男', author_id=au3.id)
    # 把数据提交给用户会话
    db.session.add_all([bk1, bk2, bk3, bk4, bk5])
    # 提交会话
    db.session.commit()
    app.run(debug=True)

